Legacy Importer
File Specs
- Format: .XLSX
- Columns:
| COLUMN NAME | COMMENTS |
|---|---|
| TERRITORY_CODE | |
| REVENUE_CLASS_DESC | |
| STATUS_DESC | |
| ACCOUNT_PKG_DESC | |
| FIRST_NAME | |
| LAST_NAME | |
| LANGUAGE_PREF_CODE | |
| S_LDC_ACCOUNT_NUM | LDC Account Number. Use text formatting. Do not allow Excel to format as a number. |
| ACCOUNT_NUM | Use the following format: XXXXXXX-XX-XXXX (These are ESG Tokens: customer_tkn, customer_acct_tkn and account_pkg_tkn) |
| PLAN_DESC | |
| CONTRACT_START_DATE | Use text formatting. Do not allow Excel to format as a date. |
| CONTRACT_END_DATE | Use text formatting. Do not allow Excel to format as a date. |
| FIXED_COMMODITY | Numeric value. Use text formatting. Do not allow Excel to format as a number. |
| FIXED_CHARGE_AMT | Numeric value. Use text formatting. Do not allow Excel to format as a number. |
| SERVICE_ADDRESS | |
| SERVICE_CITY_NAME | |
| SERVICE_STATE_CODE | |
| SERVICE_POSTAL_CODE | |
| CORP_PHONE_NUM | |
| CELLULAR_NUM | |
| HOME_PHONE_NUM | |
| MAILING_ATTENTION_NAME | |
| MAILING_LN1_ADDR | |
| MAILING_CITY_NAME | |
| MAILING_STATE_CODE | |
| MAILING_POSTAL_CODE | |
| MAILING_COUNTRY_NAME | |
| E_MAIL_ADDR | |
| TERM | Examples: 12, 24, 18. Optional. |
| Alt_email | |
| COMPANY NAME | |
| ESG_SUBSTATUS | Must be a valid ESG Substatus |
| Plan ID | Optional. When filled, OpsAdmin will assign this plan to the account. |
Processing and Validations
Account
- ACCOUNT_NUM is validated to make sure it has the required dash formatting
- ACCOUNT_NUM is used by OpsAdmin to get the exact record from Datamart_Accounts extract from ESG. This record has general information like dates, plan, rates, etc.
- System gets the account_pkg_desc (Commodity) from Datamart_Accounts extract.
- OpsAdmin validates that the account doesn’t exist in the database: LDC account and Commodity are used to make sure that the accounts does not previously exist in the system. Cancelled and Archived accounts are ignored during this search.
Customer
- Customer token is used to get Customer Information from Datamart_Customers.
- From Datamart_Customers, OpsAdmin gets the customer’s email address. If it doesn’t have one there, the email provided on the spreadsheet is used. Email format is validated and prefered contact is set to “Email”.
- When there is no email address available, the default email address is assigned. Also, Prefered contact method is set to “Phone”.
- Alternative email is also saved to the database, if provided.
- Cell number and phone number are saved in the database.
- revenue_class_desc field is retrieved from Datamart_Accounts extract to establish Customer Type (Residential, Small Business, etc).
- First Name, Last Name and Firm Name are populated from Datamart_Customers. In case there is no information in the extract, OpsAdmin will use info from the file.
- At this point, customer is saved to the database.
Zipcode
- postal_code from Datamart_Accounts is assigned as the account zipcode, unless it’s empty, in which case, the zipcode from the spreadsheet is used.
Dates
- Contract Start Date: System will attempt to use dates from Datamart_Accounts extract (contract_effective_date or start_date). If they are not available, system will use the contract start date date from the file.
- Contract End Date: System will attempt to use dates from *Datamart_Accounts *extract (contract_expiration_date or end_date). If they are not available, system will use the contract end date date from the file. If there is no Contract End Date at all, system can generate one using Term and Plan information and a valid Contract Start Date.
- Service Start Date: confirmed_start_date *from Datamart_Accounts is used to populate this field.
- Service End Date: confirmed_drop_date from Datamart_Accounts is used to populate this field.
- Requested Start Date: requested_start_date from Datamart_Accounts is used to populate this field.
Addresses
Service Address
- State: ** state_code from Datamart_Accounts is used to populate this field. The state from P2C must match State value from OpsAdmin database.
- City: city_name from Datamart_Accounts is used to populate this field. State and City description are used to filter OpsAdmin Database.
Billing Address
- State: mail_state_code from Datamart_Accounts is used to populate this field. The state from P2c must match State value from OpsAdmin database.
- City: mail_city_name from Datamart_Accounts is used to populate this field. State and City description are used to filter OpsAdmin Database.
Plan - Basic Data
Term
- If a Term has been provided, it is used to locate the customer’s plan. Otherwise, contract dates are used to establish the term. This field in involved in plan validation.
Rate
- System checks the value of fixed_commodity_amt from Datamart_Accounts and converts that to a decimal value.
- System checks the value of FIXED_COMMODITY and converts it to a decimal value.
Monthly Fee
- System checks the value of fixed_charge_amt from Datamart_Accounts and converts that to a decimal value.
- System checks the value of FIXED_CHARGE_AMT and converts it to a decimal value.
ESG Plan Code
- If there’s a value stored under price_plan_plan_desc in Datamart_Accounts, this is the ESG plan code that will be used as reference along the whole plan validation process. Plan Source is set to “Datamart”.
- If there’s no value from Datamart_Accounts, PLAN_DESC from spreadsheet is used. Plan source is set to “Excel”.
Zone
- supply_zone_desc is retrieved from Datamart_Accounts.
Utility
- distributor_name is retrieved from Datamart_Accounts.
Commodity
- account_pkg_desc is retrieved from Datamart_Accounts.
Customer Type
- revenue_class_desc from Datamart_Accounts is retrieved.
Plan Assessment Process
Note: The plan search and validation is currently being done against NON WEB PLANS.
- System gets a list of all the NON WEB PLANS from OpsAdmin Database that match the ESG Plan code.
- System filters the previous list of plan by using Customer Type from Datamart.
- System validates that the utility provided by Datamart_Accounts is available for the provided zipcode, commodity and customer type in OpsAdmin Database.
- System validates that there is a plan with the established Esg Plan Code for the Utility validated in #3 in OpsAdmin Database.
- System verifies that there is a numeric term provided. If there is no term, System calculates term by making a difference between Contract Start and End Date.
- The previously calculated term is used to filter the plans list and find a plan with that exact same term. Same process for Non Numeric Term.
- System filters the plan list to get a plan that matches the customer’s type.
- System filters the plan list to get a plan that matches the provided zipcode.
- If the provided rate (From Spreadsheet or Datamart), is not null, plan is labeled as “Fixed” and the system filters the previous plan list to get only fixed plans. Otherwise, plan is labeled as “Variable”.
- System filters the list to get a unique plan matching rate and monthly fee.
- If the system is unable to find a plan that matches that rate, operator will have the possibility of creating a new version of it. The existing plan must be versionable (Not deprecated).
- if the account is expired or close to its expiration, system will offer the chance to renew it at the moment.
- Account is saved to the database with ACCEPTED/EXPIRED status.
Exceptions and Error Messages
| Error Message | Explanation |
|---|---|
| Account data was not processed correctly. | ACCOUNT_NUM field must have dash formatting to be successfully processed. Verify that this field has the right format. |
| The system cannot get the data mart account information. | System is unable to locate the account in the Datamart_Accounts ESG extract. Verify that the ACCOUNT_NUM field is valid and exists. Verify that the ESG extracts are up to date. |
| The system cannot get the commodity information. | The field account_pkg_desc from Datamart_Accounts is empty. |
| Our records indicate an account already exists. Please contact Customer Service for additional assistance at 1-800-324-3046. | LDC already exists in the system. |
| The system cannot get the customer data mart information. | The system is unable to locate the customer in the Datamart_Customer extract from ESG. |
| The email address is not valid: "{0}". Please correct and resubmit or leave blank. | Email address should have this format: XXXXXX@XXXX.XXX |
| The system cannot get the customer type information. | Unable to retrieve the revenue_class_desc field from Datamart_Accounts. |
| The system cannot get the customer information. | Unable to Save Customer to Database |
| Opt In field is required and it must be Y or N. | Invalid value for OPT_IN in spreadsheet. |
| The system cannot get the state address information. | The state that the customer has in Datamart_Accounts field state_code doesn’t match OpsAdmin state’s table. |
| The system cannot get the city address information. | The city under Datamart_Accounts doesn’t match OpsAdmin city’s table. |
| Fixed_Commodity_Amt: [Error Message] | fixed_commodity_amt (Datamart_Accounts) and FIXED_COMMODITY (Spreadsheet) must be numeric values. |
| Fixed_Charge_Amt: [Error Message] | fixed_charge_amt (Datamart_Accounts) and FIXED_CHARGE_AMT must be numeric values. |
| ESG Plan Code not found in OE Admin. | The ESG Plan code that the Customer has in Datamart_Accounts doesn’t exist in OpsAdmin database. |
| Plan not found for this customer type in OE Admin. | The Customer Type that the customer has in Datamart_Accounts doesn’t exist in OpsAdmin database for the provided ESG Plan Code. |
| Non-existing ZipCode in OE Admin. | Zipcode loaded from Datamart_Accounts or from Spreadsheet doesn’t exist in OpsAdmin Database. |
| ESG utility name not found in OE Admin for this commodity and Zipcode. | The provided plan, commodity type, customer and zone do not exist in OpsAdmin Database for the provided Utility by Datamart_Accounts. |
| Plan not found for this utility in OE Admin. | The provided ESG Plan code is not associated to the provided Utility in OpsAdmin Database. |
| There was an error geting the term | System was unable to establish a term when doing a calculation between Contract Start and Contract End Date. |
| The system can not get the enrollment substatus information. | The substatus provided is not a valid ESG Substatus. |
| Plan not found for this term in OE Admin. | System couldn’t find a plan for the provided numeric term/non numeric term/contract dates calculation |
| Plan not found for this customer type in OE Admin. | The Customer type is not associated with this plan. |
| Plan not associated to Customer's zip code. | The provided zipcode does not belong to the Customer’s plan. |
| Spreadsheet rate must be higher than one. | Rate format must be higher than one. For example: 5.12 and not 0.512 |
| A new version of plan [Plan Name] will be created. The rate of the plan is currently [Current Rate] and the new version will have a rate of [New Rate]. The monthly fee of the plan is currently [Current Monthly Fee] and the new version will have a monthly fee of [New Monthly Fee]. | Alert shown to the user when the system is unable to find a plan that matches the provided rate (From Spreadsheet/Datamart) |
| Not approved the new version of the plan. | Not approved the new version of the plan. |
| There was an error verifying if the plan was versionable | System could not find a versionable plan. |
| The Plan is not versionable: Is deprecated or is not published yet. | The importer is trying to create a new version of a plan and is unable to do it because the newest plan is deprecated. |
| Cannot find rate value in OE Admin. | Importer cannot find in the system a plan that matches the provided rate. |
| Plan not found for this plan type in OE Admin. | System couldn’t find a variable o fixed plan that matches the search. |
| ESG Plan Code not found in OE Admin. | The provided ESG Plan codes was not found in the system. |
| This enrollment is current eligible for a Renewal | The account is EXPIRED or close to its expiration date (Check Market Manager). |